#-*- coding: utf-8 -*-
'''
@author Filipe Mestre & Andre Ventura
@date: 2 de Dezembro de 2013
@obs: Módulo referente à leitura do ficheiro em formato .xls
e criação/escrita da base de dados.
Método referente à leitura da base de dados sqlite3 (consultas),
e criação/escrita dos ficheiros CSV's com os dados das respectivas
consultas.
'''
import xlrd
import sqlite3
import csv
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import *

 
base = declarative_base()
db_session = scoped_session(sessionmaker())

'''
	Cria a variável __tablename__ com uma tabela 'tabela_de_dados'
	e com as definições das colunas que vão ser depois preenchidas.
'''
class Createtabela(base):
	
	__tablename__ = 'tabela_de_dados'   
	id = Column(Integer, primary_key=True)
	NomeDaInstituicao = Column(String(100))
	NomeDoCurso =  Column(String(100))
	TotalDeVagas = Column(String(30))
	Colocados = Column(String(30))
	VagasSobrantes = Column(String(30))

	IDX_INSTITUICAO = 1
	IDX_CURSO = 2
	IDX_VAGAS = 3
	IDX_COLOCADOS = 4
	IDX_SOBRANTES = 5


def xlread(xls_doc):

	'''
	Esta função cria um ficheiro de base de dados, e coloca
	Instituicoes, Cursos, Vagas, Numero de Colocados e Vagas sobrantes
	ordenadas em colunas.

	xls_doc: Ficheiro Excel a ser lido
	'''

	# Abre o documento
	excel = xlrd.open_workbook(xls_doc)
	engine = create_engine('sqlite:///basedados.db', echo=True)
	db_session.configure(bind=engine, autoflush=False, expier_on_commit=False)
	base.metadata.drop_all(engine)
	base.metadata.create_all(engine)

	excel_sheet = excel.sheets()[0]
	engine.execute(
		Createtabela.__table__.insert(),
		[{"NomeDaInstituicao": ((excel_sheet.cell(line, 2).value).split(' -')[0]),
		"NomeDoCurso": excel_sheet.cell(line, 3).value,
		"TotalDeVagas": excel_sheet.cell(line, 5).value,
		"Colocados": excel_sheet.cell(line, 6).value,
		"VagasSobrantes": excel_sheet.cell(line, 8).value} for line in range(3, excel_sheet.nrows - 2)]
		)           										 
	

def estatistica_Instituicao(flag = True):

	'''
	Calcula o numero de colocados, vagas sobrantes,
	a percentagem de colocados por Instituição e 
	cria um ficheiro .csv com com a opção de escrita
	Se existir substitui
	A lista preenchida é escrita no ficheiro criado
	'''
	baseDados = create_engine('sqlite:///basedados.db')
	metadata = MetaData(baseDados)
	tabelaDados = Table('tabela_de_dados', metadata, autoload = True)
	result= tabelaDados.select()
	results = result.execute()
	marks = ''
	list1 = []
	total_colocados = 0.0
	for line in results:
		total_colocados += float(line[Createtabela.IDX_COLOCADOS])
		if line[Createtabela.IDX_INSTITUICAO] not in list1:
			list1.append(line[Createtabela.IDX_INSTITUICAO])


	list2 = []
	for i in list1:
		colocados = 0.0
		sobrantes = 0.0
		result = tabelaDados.select(tabelaDados.c.NomeDaInstituicao.like(i)).execute()
		for line in result:
			colocados +=float(line[Createtabela.IDX_COLOCADOS])
			sobrantes +=float(line[Createtabela.IDX_SOBRANTES])
		list2.append([i.encode('utf-8'), int(colocados), int(sobrantes), float((colocados / total_colocados) * 100)])

	writer = csv.writer( open( "estatistica_Instituicao.csv", 'w'), delimiter = ';')
	writer.writerows( list2 )
	return list2
	

def estatistica_Distrito():
	'''
	Calcula o numero de colocados, vagas sobrantes,
	a percentagem de colocados por Distrito e 
	cria um ficheiro .csv com com a opção de escrita
	Se existir substitui
	A lista preenchida é escrita no ficheiro criado
	'''
	baseDados = create_engine('sqlite:///basedados.db')
	metadata = MetaData(baseDados)
	tabelaDados = Table('tabela_de_dados', metadata, autoload = True)

	result= tabelaDados.select()
	results = result.execute()

	total_colocados = 0.0
	for line in results:
		total_colocados += float(line[Createtabela.IDX_COLOCADOS])

	
	Session = sessionmaker(bind=baseDados)
	session = Session()

	distritos = {      
	u'Madeira': [u'Madeira'], 
	u'Açores': [u'Açores'],
	u'Viana do Castelo': [u'Viana do Castelo'],
	u'Bragança': [u'Bragança'],
	u'Vila Real': [u'Trás-os-Montes e Alto Douro'],
	u'Braga': [u'Minho', u'Cávado e do Ave'],
	u'Porto': [u'Porto'],
	u'Guarda': [u'Guarda'], 
	u'Viseu': [u'Viseu'],
	u'Aveiro': [u'Aveiro'],
	u'Coimbra': [u'Coimbra'],
	u'Castelo Branco': [u'Castelo Branco'],
	u'Leiria': [u'Leiria'],
	u'Santarém': [u'Santarém', u'Tomar', u'Beira Interior'], 
	u'Portalegre': [u'Portalegre'],
	u'Lisboa': [u'Lisboa', u'Estoril', u'ISCTE', u'Infante D. Henrique'],
	u'Évora': [u'Évora'],
	u'Setúbal': [u'Setúbal'],
	u'Beja': [u'Beja'],
	u'Faro': [u'Algarve']
	}
	

	list3 = []
	for distrito, insts in distritos.iteritems():
		somaColocados = 0.0
		somaVagas = 0.0

		
		for local in insts:
			for instituicao in session.query(Createtabela).filter(Createtabela.NomeDaInstituicao.like("%" + local + "%")):
				inst = instituicao.NomeDaInstituicao
			#	print inst.encode('utf-8'), instituicao.NomeDoCurso.encode('utf-8') + "   " + str(instituicao.Colocados)
				somaColocados += float(instituicao.Colocados)
				somaVagas += float(instituicao.VagasSobrantes)
			
		list3.append([distrito.encode('utf-8'), int(somaColocados), int(somaVagas), float((somaColocados / total_colocados) * 1000) ])
		
		#print distrito.encode('utf-8') + ": " + str(somaColocados) + "\t - " + str(somaVagas) + "\t - " + str((somaColocados / total_colocados) * 1000)
	
		
	writer = csv.writer( open( "estatistica_distrito.csv", 'w'), delimiter = ';')
	writer.writerows( list3 )
	return list3


    
